﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Configuration;
using System.Data.Linq.SqlClient;
using System.Data.SqlClient;
using System.Data;

namespace Cosmetic.Logic
{
    /// <summary>
    /// 
    /// </summary>
    public class PurchaseManager : LogicBase
    {
        //CosmeticORMDataContext ctx;


        //public PurchaseManager(string conn)
        //{
        //    ctx = new ShangHaiHzDataContext (conn);
        //}



        /// <summary>
        /// 根据User取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public IEnumerable getOrdersByUser(User user)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.UserId
                         select ordInfo;
            return result;
        }

        public IEnumerable getOrderByUser(User user, string orderNum)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.UserId
                         select ordInfo;
            result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result;
        }

        public IEnumerable getOrderByUser(User user, string orderNum, DateTime aDateTime)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.UserId
                         && ordInfo.CreateTime > aDateTime
                         select ordInfo;
            result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result;
        }
        //ali 090223
        public string orderno = "";
        //ali090223
        Order order = null;
        /// <summary>
        /// 生成订单
        /// </summary>
        /// <param name="user">Cosmetic.DataEntity.User,取UserId</param>
        /// <param name="ordernum"></param>
        public Guid InsertOrder(string Remark, User user, string DeliveryType, DateTime SendDate, string DeliveryTime, string PayType, string Price, string DeliveryUserName, string DeliveryAddress, string DeliveryPostCode, string DeliveryTel, string DeliveryCellPhone, Hashtable ProductIds)
        {
            //OrderDetail ordetl = new OrderDetail();
            Guid orderid = Guid.NewGuid();
            Order ord = new Order();
            ord.OrderId = orderid;
            ord.UserId = user.UserId;
            ord.AddressId = new Guid("f0241e6d-b0af-4911-bc45-af3688dad592");//user.Addresses[0].AddressId;
            ord.CreateTime = DateTime.Now;
            ord.SendDate = SendDate;
            ord.DeliveryType = DeliveryType;
            ord.DeliveryTime = DeliveryTime;
            ord.PayType = PayType;
            ord.State = "未发货";//2种状态：未发货，交易完成
            ord.Price = Convert.ToDecimal(Price);
            ord.OrderNum = CreateOrderNum();
            orderno = ord.OrderNum;
            ord.DeliveryAddress = DeliveryAddress;
            ord.DeliveryCellPhone = DeliveryCellPhone;
            ord.DeliveryPostCode = DeliveryPostCode;
            ord.DeliveryTel = DeliveryTel;
            ord.DeliveryUserName = DeliveryUserName;
            ord.Remark = Remark;


            //Address add = new Address();
            //add.AddressId = Guid.NewGuid();
            //add.UserId = "07c684d3-910f-44fd-9781-2dee610b59e8";
            //Express exp = new Express();
            order = ord;
            m_CosmeticDC.Orders.InsertOnSubmit(ord);

            foreach (DictionaryEntry item in ProductIds)
            {
                OrderDetail od = new OrderDetail();
                od.OrderDetailId = Guid.NewGuid();
                od.OrderId = orderid;
                od.ProductId = new Guid(item.Key.ToString());
                od.Count = int.Parse(item.Value.ToString());
                od.ProductCode = (from prod in m_CosmeticDC.ProductInfos where prod.ProductId == new Guid(item.Key.ToString()) select prod).Single().ProductCode;
                m_CosmeticDC.OrderDetails.InsertOnSubmit(od);
            }

            m_CosmeticDC.SubmitChanges();

            return orderid;
            //D0712190001
        }

        public void InsertOrder(Order aOrder, Hashtable aHashtable )
        {
            m_CosmeticDC.ExecuteCommand(@"INSERT INTO dbo.[Order] (OrderId, UserId, PayType, CreateTime, OrderNum, DeliveryType, AddressId, SendDate, State, Remark, Price, DeliveryUserName, DeliveryAddress, DeliveryPostCode, DeliveryTel, DeliveryCellPhone, Valid, Intergral,DeliveryPrice,IsBook,UseIntegral,UseCashCard,UseDiscountCard,adress1,adress2,adress3,adress4,tel2,month,allprice,Year,ExpressNum) 
                                                                 values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31)",
                aOrder.OrderId, aOrder.UserId, aOrder.PayType, aOrder.CreateTime, aOrder.OrderNum, aOrder.DeliveryType, aOrder.AddressId, aOrder.SendDate, aOrder.State, aOrder.Remark,
                aOrder.Price, aOrder.DeliveryUserName, aOrder.DeliveryAddress, aOrder.DeliveryPostCode, aOrder.DeliveryTel, aOrder.DeliveryCellPhone, aOrder.Valid, aOrder.Intergral, aOrder.DeliveryPrice, aOrder.IsBook, aOrder.UseIntegral, aOrder.UseCashCard, aOrder.UseDiscountCard, aOrder.adress1, aOrder.adress2, aOrder.adress3, aOrder.adress4, aOrder.tel2, aOrder.month, aOrder.allprice, aOrder.Year,aOrder.ExpressNum);

            if (aHashtable != null)
            {
                foreach (DictionaryEntry item in aHashtable)
                {
                    OrderDetail theOrderDetail = new OrderDetail();
                    theOrderDetail.OrderDetailId = Guid.NewGuid();
                    theOrderDetail.OrderId = aOrder.OrderId;
                    theOrderDetail.ProductId = new Guid(item.Key.ToString());
                    theOrderDetail.Count = int.Parse(item.Value.ToString());
                    theOrderDetail.ProductCode = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == new Guid(item.Key.ToString()) select theProduct).Single().ProductCode;
                    m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail](OrderDetailId, OrderId, ProductId, Count, ProductCode, Size) values (@p0, @p1, @p2, @p3, @p4, @p5)", theOrderDetail.OrderDetailId, theOrderDetail.OrderId, theOrderDetail.ProductId, theOrderDetail.Count, theOrderDetail.ProductCode, "");
                    //m_CosmeticDC.OrderDetails.InsertOnSubmit(theOrderDetail);
                }
            }
            

            //m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// aHashtable_dis 是捆绑销售的商品。
        /// </summary>
        /// <param name="aOrder"></param>
        /// <param name="aHashtable"></param>
        /// <param name="aHashtable_dis"></param>
        public void InsertOrder(Order aOrder, Hashtable aHashtable, Hashtable aHashtable_dis)
        {
            m_CosmeticDC.ExecuteCommand(@"INSERT INTO dbo.[Order] (OrderId, UserId, PayType, CreateTime, OrderNum, DeliveryType, AddressId, SendDate, State, Remark, Price, DeliveryUserName, DeliveryAddress, DeliveryPostCode, DeliveryTel, DeliveryCellPhone, Valid, Intergral,DeliveryPrice,IsBook,UseIntegral,UseCashCard,UseDiscountCard,adress1,adress2,adress3,adress4,tel2,month,allprice,Year,ExpressNum) 
                                                                 values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31)",
                aOrder.OrderId, aOrder.UserId, aOrder.PayType, aOrder.CreateTime, aOrder.OrderNum, aOrder.DeliveryType, aOrder.AddressId, aOrder.SendDate, aOrder.State, aOrder.Remark,
                aOrder.Price, aOrder.DeliveryUserName, aOrder.DeliveryAddress, aOrder.DeliveryPostCode, aOrder.DeliveryTel, aOrder.DeliveryCellPhone, aOrder.Valid, aOrder.Intergral, aOrder.DeliveryPrice, aOrder.IsBook, aOrder.UseIntegral, aOrder.UseCashCard, aOrder.UseDiscountCard, aOrder.adress1, aOrder.adress2, aOrder.adress3, aOrder.adress4, aOrder.tel2, aOrder.month, aOrder.allprice, aOrder.Year,aOrder.ExpressNum);

            if (aHashtable != null)
            {
                foreach (DictionaryEntry item in aHashtable)
                {
                    OrderDetail theOrderDetail = new OrderDetail();
                    theOrderDetail.OrderDetailId = Guid.NewGuid();
                    theOrderDetail.OrderId = aOrder.OrderId;
                    theOrderDetail.ProductId = new Guid(item.Key.ToString());
                    theOrderDetail.Count = int.Parse(item.Value.ToString());
                    theOrderDetail.ProductCode = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == new Guid(item.Key.ToString()) select theProduct).Single().ProductCode;
                    m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail](OrderDetailId, OrderId, ProductId, Count, ProductCode, Size) values (@p0, @p1, @p2, @p3, @p4, @p5)", theOrderDetail.OrderDetailId, theOrderDetail.OrderId, theOrderDetail.ProductId, theOrderDetail.Count, theOrderDetail.ProductCode, "");
                    //m_CosmeticDC.OrderDetails.InsertOnSubmit(theOrderDetail);
                }
            }
            if (aHashtable_dis != null)
            {
                foreach (DictionaryEntry item in aHashtable_dis)
                {
                    OrderDetail theOrderDetail = new OrderDetail();
                    theOrderDetail.OrderDetailId = Guid.NewGuid();
                    theOrderDetail.OrderId = aOrder.OrderId;
                    theOrderDetail.ProductId = new Guid(item.Key.ToString());
                    theOrderDetail.Count = int.Parse(item.Value.ToString());
                    theOrderDetail.ProductCode = (from theProduct in m_CosmeticDC.Products where theProduct.ProductId == new Guid(item.Key.ToString()) select theProduct).Single().ProductCode;
                    m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail](OrderDetailId, OrderId, ProductId, Count, ProductCode, Size) values (@p0, @p1, @p2, @p3, @p4, @p5)", theOrderDetail.OrderDetailId, theOrderDetail.OrderId, theOrderDetail.ProductId, theOrderDetail.Count, theOrderDetail.ProductCode, "");
                    
                }
            }

            //m_CosmeticDC.SubmitChanges();
        }

        public void InsertOrders(Order aOrder)
        {

            m_CosmeticDC.ExecuteCommand(@"INSERT INTO dbo.[Order] (OrderId, UserId, PayType, CreateTime, OrderNum, DeliveryType, AddressId, SendDate, State, Remark, DeliveryTime, Price, DeliveryUserName, DeliveryAddress, DeliveryPostCode, DeliveryTel, DeliveryCellPhone, Valid, Intergral,DeliveryPrice,IsBook,UseIntegral,UseCashCard,UseDiscountCard,UsedDiscountCardId,UsedCashCardId) 
                                                                 values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20,@p21,@p22,@p23,@p24,@p25)",
                aOrder.OrderId, aOrder.UserId, aOrder.PayType, aOrder.CreateTime, aOrder.OrderNum, aOrder.DeliveryType, aOrder.AddressId, aOrder.SendDate, aOrder.State, aOrder.Remark, aOrder.DeliveryTime,
                aOrder.Price, aOrder.DeliveryUserName, aOrder.DeliveryAddress, aOrder.DeliveryPostCode, aOrder.DeliveryTel, aOrder.DeliveryCellPhone, aOrder.Valid, aOrder.Intergral, aOrder.DeliveryPrice, aOrder.IsBook, aOrder.UseIntegral, aOrder.UseCashCard, aOrder.UseDiscountCard, aOrder.UsedDiscountCardId, aOrder.UsedCashCardId);
        }

        public void InsertOrderIntegral(Order aOrder)
        {
            //theOrder.OrderId = Guid.NewGuid();
            //theOrder.PayType = "积分";
            //theOrder.UseIntegral = decimal.Parse(hfePrice.Value);
            //theOrder.UserId = this.SignInUser.UserId;
            //theOrder.CreateTime = DateTime.Now;
            //theOrder.OrderNum = thePurchaseManager.CreateOrderNum();
            //theOrder.Valid = true;
            //theOrder.State = "完成";
            m_CosmeticDC.ExecuteCommand(@"INSERT INTO dbo.[Order] (OrderId, UserId, PayType, CreateTime, OrderNum, State, Valid,UseIntegral,DeliveryType,AddressId) 
                                                                 values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7,@p8,@p9)",
                aOrder.OrderId, aOrder.UserId, aOrder.PayType, aOrder.CreateTime, aOrder.OrderNum, aOrder.State,
                aOrder.Valid, aOrder.UseIntegral, aOrder.DeliveryType, aOrder.AddressId);
        }
        public void insertordersdelt(OrderDetail aOrder)
        {

            m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail](OrderDetailId, OrderId, ProductId, Count, ProductCode, Size) values (@p0, @p1, @p2, @p3, @p4, @p5)", aOrder.OrderDetailId, aOrder.OrderId, aOrder.ProductId, aOrder.Count, aOrder.ProductCode, "");

        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="aOrder">订单</param>
        /// <param name="aHashtable">订单物品详细信息(包含每个物品的PID和数量<pid,count>)</param>
        /// <param name="bHashtable">订单物品尺寸信息(<pid,sizeName>)</param>
        public void InsertOrder(Order aOrder, Hashtable aHashtable, Hashtable packageHashtable, Hashtable bHashtable)
        {
            m_CosmeticDC.ExecuteCommand(@"INSERT INTO dbo.[Order] (OrderId, UserId, PayType, CreateTime, OrderNum, DeliveryType, AddressId, SendDate, State, Remark, DeliveryTime, Price, DeliveryUserName, DeliveryAddress, DeliveryPostCode, DeliveryTel, DeliveryCellPhone, Valid, Intergral,DeliveryPrice,IsBook,UseIntegral,UseCashCard,UseDiscountCard,UsedDiscountCardId,UsedCashCardId) 
                                                                 values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19,@p20,@p21,@p22,@p23,@p24,@p25)",
            aOrder.OrderId, aOrder.UserId, aOrder.PayType, aOrder.CreateTime, aOrder.OrderNum, aOrder.DeliveryType, aOrder.AddressId, aOrder.SendDate, aOrder.State, aOrder.Remark, aOrder.DeliveryTime,
            aOrder.Price, aOrder.DeliveryUserName, aOrder.DeliveryAddress, aOrder.DeliveryPostCode, aOrder.DeliveryTel, aOrder.DeliveryCellPhone, aOrder.Valid, aOrder.Intergral, aOrder.DeliveryPrice, aOrder.IsBook, aOrder.UseIntegral, aOrder.UseCashCard, aOrder.UseDiscountCard, aOrder.UsedDiscountCardId, aOrder.UsedCashCardId);
            //insert order detail
            if (aHashtable != null)
            {
                foreach (DictionaryEntry item in aHashtable)
                {
                    OrderDetail theOrderDetail = new OrderDetail();
                    theOrderDetail.OrderDetailId = Guid.NewGuid();
                    theOrderDetail.OrderId = aOrder.OrderId;
                    theOrderDetail.ProductId = new Guid(item.Key.ToString());
                    theOrderDetail.Count = int.Parse(item.Value.ToString());
                    theOrderDetail.ProductCode = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == new Guid(item.Key.ToString()) select theProduct).Single().ProductCode;

                    if (bHashtable.Contains(item.Key.ToString()))
                    {
                        theOrderDetail.Size = bHashtable[item.Key.ToString()].ToString();
                    }
                    else
                    {
                        theOrderDetail.Size = "";
                    }
                    m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail](OrderDetailId, OrderId, ProductId, Count, ProductCode, Size) values (@p0, @p1, @p2, @p3, @p4, @p5)", theOrderDetail.OrderDetailId, theOrderDetail.OrderId, theOrderDetail.ProductId, theOrderDetail.Count, theOrderDetail.ProductCode, theOrderDetail.Size);

                    //m_CosmeticDC.OrderDetails.InsertOnSubmit(theOrderDetail);
                }
            }

            if (packageHashtable != null)
            {
                foreach (DictionaryEntry item in packageHashtable)
                {
                    OrderDetail theOrderDetail = new OrderDetail();
                    theOrderDetail.OrderDetailId = Guid.NewGuid();
                    theOrderDetail.OrderId = aOrder.OrderId;
                    theOrderDetail.ProductId = new Guid(item.Key.ToString());
                    theOrderDetail.Count = int.Parse(item.Value.ToString());
                    theOrderDetail.ProductCode = "";// (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == new Guid(item.Key.ToString()) select theProduct).Single().ProductCode;
                    theOrderDetail.PackageId = new Guid(item.Key.ToString());


                    if (bHashtable.Contains(item.Key.ToString()))
                    {
                        theOrderDetail.Size = bHashtable[item.Key.ToString()].ToString();
                    }
                    else
                    {
                        theOrderDetail.Size = "";
                    }
                    m_CosmeticDC.ExecuteCommand("INSERT  INTO dbo.[OrderDetail] values (@p0, @p1, @p2, @p3, @p4, @p5,@p6)", theOrderDetail.OrderDetailId, theOrderDetail.OrderId, theOrderDetail.ProductId, theOrderDetail.Count, theOrderDetail.ProductCode, theOrderDetail.Size, theOrderDetail.PackageId);

                }
            }
        }

        //private string CreateOrderNum()
        //{
        //    string CompareNum = "D"+DateTime.Today.Year.ToString().Substring(2,2)+DateTime.Today.Month.ToString("00")+DateTime.Today.Day.ToString("00");

        //    int MaxOrder = 0;
        //    MaxOrder = ((from ord in m_CosmeticDC.Orders
        //               select ord).Where(c=>SqlMethods.Like(c.OrderNum,"%"+CompareNum+"%"))).Count();

        //    return CompareNum + (MaxOrder + 1).ToString("0000");
        //}
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string CreateOrderNum()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ShangHaiHZConnectionString"].ConnectionString);

            string CompareNum = "D" + DateTime.Today.Year.ToString().Substring(2, 2) + DateTime.Today.Month.ToString("00") + DateTime.Today.Day.ToString("00");

            DataSet ds = new DataSet();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "select count(*) from 出货主表 where  substring(出货单号,1,7) = '" + CompareNum + "'";

                cmd.Connection = con;

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }





            //List<Order> theOrders   = (from theOrder in m_CosmeticDC.Orders
            //                      where theOrder.OrderNum.StartsWith(CompareNum)
            //                      select theOrder).ToList();

            //if (theOrders.Count() == 0)
            //{
            //    return CompareNum + "5001";
            //}
            if (ds.Tables[0].Rows.Count == 0)
            {
                return CompareNum + "5001";
            }

            DataSet ds2 = new DataSet();
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "select max(出货单号) from 出货主表 where substring(出货单号,1,7) = '" + CompareNum + "'";

                cmd.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds2);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (ds2.Tables[0].Rows[0][0].ToString().Length < 8)
            {
                return CompareNum + "5001";
            }

            return CompareNum + (Convert.ToInt32(ds2.Tables[0].Rows[0][0].ToString().Substring(8, 3)) + 1).ToString("5000");

        }


        /// <summary>
        ///    写出货主表及客户付款记录表存储过程(P_Delivery)
        ///    商品出货表(P_DeliveryList)
        /// </summary>
        public void CreateOrderInHZ(string OrderNum, string CustomerCode, string MoneyType, string OperateMan)
        {
            //MoneyType = "支付宝"
            Order order = (from od in m_CosmeticDC.Orders
                           where od.OrderNum == OrderNum
                           orderby od.CreateTime descending
                           select od).Take(1).Single();



            string conStr = ConfigurationManager.ConnectionStrings["ShangHaiHZConnectionString"].ConnectionString;

            SqlConnection con = new SqlConnection(conStr);
            con.Open();



            try
            {
                string cmdStr = "P_Delivery";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add("@DeliveryId", SqlDbType.NVarChar);
                com.Parameters["@DeliveryId"].Value = OrderNum;
                com.Parameters["@DeliveryId"].Direction = ParameterDirection.InputOutput;


                com.Parameters.Add("@CustomerCode", SqlDbType.NVarChar);
                com.Parameters["@CustomerCode"].Value = CustomerCode;

                com.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                com.Parameters["@CustomerName"].Value = order.DeliveryUserName;

                com.Parameters.Add("@Phone", SqlDbType.NVarChar);
                com.Parameters["@Phone"].Value = order.DeliveryTel;

                com.Parameters.Add("@Mobile", SqlDbType.NVarChar);
                com.Parameters["@Mobile"].Value = order.DeliveryCellPhone;

                com.Parameters.Add("@Fax", SqlDbType.NVarChar);
                com.Parameters["@Fax"].Value = "";

                com.Parameters.Add("@Post", SqlDbType.NVarChar);
                com.Parameters["@Post"].Value = order.DeliveryPostCode;

                com.Parameters.Add("@Addr1", SqlDbType.NVarChar);
                com.Parameters["@Addr1"].Value = order.DeliveryAddress;

                com.Parameters.Add("@DeliveryDate", SqlDbType.DateTime);
                com.Parameters["@DeliveryDate"].Value = Convert.ToDateTime(order.CreateTime.ToString("d"));

                com.Parameters.Add("@MoneyType", SqlDbType.NVarChar);
                com.Parameters["@MoneyType"].Value = MoneyType;

                com.Parameters.Add("@OperateMan", SqlDbType.NVarChar);
                com.Parameters["@OperateMan"].Value = OperateMan;

                com.Parameters.Add("@TractMoney", SqlDbType.Decimal);
                com.Parameters["@TractMoney"].Value = order.DeliveryPrice;

                com.Parameters.Add("@DiscountTotal", SqlDbType.Decimal);
                com.Parameters["@DiscountTotal"].Value = order.Price;

                com.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                com.Parameters["@CustomerType"].Value = "网站客户";


                com.Parameters.Add("@Remark", SqlDbType.NVarChar);
                if (order.Remark == null) { com.Parameters["@Remark"].Value = ""; }
                else
                {
                    com.Parameters["@Remark"].Value = order.Remark;
                }

                com.Parameters.Add("@DeliveryType", SqlDbType.NVarChar);
                com.Parameters["@DeliveryType"].Value = "零售";

                com.Parameters.Add("@Shop", SqlDbType.NVarChar);
                com.Parameters["@Shop"].Value = "";

                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        public class ProductDetail
        {

            string size;

            public string Size
            {
                get { return size; }
                set { size = value; }
            }

            string productCode;

            public string ProductCode
            {
                get { return productCode; }
                set { productCode = value; }
            }
            string productBarCode;

            public string ProductBarCode
            {
                get { return productBarCode; }
                set { productBarCode = value; }
            }

            string productName;

            public string ProductName
            {
                get { return productName; }
                set { productName = value; }
            }

            string productJapanName;

            public string ProductJapanName
            {
                get { return productJapanName; }
                set { productJapanName = value; }
            }

            int count;

            public int ProductCount
            {
                get { return count; }
                set { count = value; }
            }

            decimal productPrice;

            public decimal ProductPrice
            {
                get { return productPrice; }
                set { productPrice = value; }
            }
        }
        public void CreateOrderDetailInHZ(string OrderNum)
        {

            string OperateMan = "Admin";
            string KC = "仓库1";
            Order rd = (from det in m_CosmeticDC.Orders where det.OrderNum == OrderNum orderby det.CreateTime descending select det).Take(1).Single();
            List<ProductDetail> Pdetail = (from detls in m_CosmeticDC.OrderDetails
                                           join proInfo in m_CosmeticDC.ProductInfos
                                               on detls.ProductCode equals proInfo.ProductCode
                                           where detls.OrderId == rd.OrderId
                                           select
                                              new ProductDetail
                                              {
                                                  ProductCode = detls.ProductCode,
                                                  ProductBarCode = proInfo.商品条形码,
                                                  ProductName = proInfo.商品名,
                                                  ProductJapanName = proInfo.商品日文名,
                                                  ProductCount = detls.Count,
                                                  ProductPrice = proInfo.网络零售价,
                                                  Size = detls.Size
                                              }
                                                 ).ToList();




            string conStr = ConfigurationManager.ConnectionStrings["ShangHaiHZConnectionString"].ConnectionString;

            SqlConnection con = new SqlConnection(conStr);
            con.Open();



            try
            {

                foreach (ProductDetail pd in Pdetail)
                {
                    string cmdStr = "P_DeliveryList";
                    SqlCommand com = new SqlCommand(cmdStr, con);
                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.Add("@ID", SqlDbType.NVarChar);
                    com.Parameters["@ID"].Value = "123";//乱写一个，查不到会新建一个GUID的
                    com.Parameters["@ID"].Direction = ParameterDirection.InputOutput;


                    com.Parameters.Add("@DeliveryId", SqlDbType.NVarChar);
                    com.Parameters["@DeliveryId"].Value = OrderNum;


                    com.Parameters.Add("@BarCode", SqlDbType.NVarChar);
                    com.Parameters["@BarCode"].Value = pd.ProductBarCode;

                    com.Parameters.Add("@GoodsCode", SqlDbType.NVarChar);
                    com.Parameters["@GoodsCode"].Value = pd.ProductCode;

                    com.Parameters.Add("@GoodsName", SqlDbType.NVarChar);
                    com.Parameters["@GoodsName"].Value = pd.ProductName;

                    com.Parameters.Add("@JapanName", SqlDbType.NVarChar);
                    com.Parameters["@JapanName"].Value = pd.ProductJapanName;

                    com.Parameters.Add("@DeliveryNumber", SqlDbType.Int);
                    com.Parameters["@DeliveryNumber"].Value = pd.ProductCount;

                    com.Parameters.Add("@DeliveryPrice", SqlDbType.Decimal);
                    com.Parameters["@DeliveryPrice"].Value = pd.ProductPrice;

                    com.Parameters.Add("@DeliveryTotal", SqlDbType.Decimal);
                    com.Parameters["@DeliveryTotal"].Value = pd.ProductCount * pd.ProductPrice;

                    com.Parameters.Add("@Discount", SqlDbType.Decimal);
                    com.Parameters["@Discount"].Value = 1;

                    com.Parameters.Add("@DiscountPrice", SqlDbType.Decimal);
                    com.Parameters["@DiscountPrice"].Value = pd.ProductPrice;

                    com.Parameters.Add("@DiscountTotal", SqlDbType.Decimal);
                    com.Parameters["@DiscountTotal"].Value = pd.ProductCount * pd.ProductPrice;

                    com.Parameters.Add("@Leader", SqlDbType.NVarChar);
                    com.Parameters["@Leader"].Value = "";

                    com.Parameters.Add("@Kc", SqlDbType.NVarChar);
                    com.Parameters["@Kc"].Value = KC;

                    com.Parameters.Add("@OperateDate", SqlDbType.DateTime);
                    com.Parameters["@OperateDate"].Value = DateTime.Now;

                    com.Parameters.Add("@OperateMan", SqlDbType.NVarChar);
                    com.Parameters["@OperateMan"].Value = OperateMan;

                    com.Parameters.Add("@TractMoney", SqlDbType.Decimal);
                    com.Parameters["@TractMoney"].Value = 0;

                    com.Parameters.Add("@GoodsType", SqlDbType.NVarChar);
                    com.Parameters["@GoodsType"].Value = "完好商品";

                    com.Parameters.Add("@Size", SqlDbType.NVarChar);
                    com.Parameters["@Size"].Value = pd.Size;

                    com.Parameters.Add("@Color", SqlDbType.NVarChar);
                    com.Parameters["@Color"].Value = "";

                    com.ExecuteNonQuery();
                }
            }
            finally
            {
                con.Close();
            }
        }



        /// <summary>
        /// get Province
        /// </summary>
        /// <returns></returns>
        public List<ChinaCity> GetProvince()
        {
            var result = (from city in m_CosmeticDC.ChinaCities
                          where city.CityPostCode.Substring(2, 4) == "0000"
                          select city).ToList();
            return result;
        }
        //得到区
        public List<ChinaCity> GetCity(string ProvinceCode)
        {
            var result = (from city in m_CosmeticDC.ChinaCities
                          where city.CityPostCode.Substring(0, 2) == ProvinceCode
                          && city.CityPostCode.Substring(2, 2) == "01"
                          select city).ToList();
            return result;
        }
        //得到县
        public List<ChinaCity> GetDistrict(string ProvinceCode)
        {
            var result = (from city in m_CosmeticDC.ChinaCities
                          where city.CityPostCode.Substring(0, 2) == ProvinceCode
                          && city.CityPostCode.Substring(2, 2) == "02"
                          select city).ToList();
            return result;
        }

        public bool ValidateOrder(string trade_no)
        {
            try
            {
                var result = from ord in m_CosmeticDC.Orders
                             where ord.OrderNum == trade_no
                             && ord.Valid == true
                             select ord;
                if (result.Count() != 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void UpdateOrder(string trade_no)
        {
            try
            {
                var result = from ord in m_CosmeticDC.Orders
                             where ord.OrderNum == trade_no
                             orderby ord.CreateTime descending
                             select ord;
                Order theOrder = result.Take(1).Single();
                theOrder.Valid = true;

                m_CosmeticDC.SubmitChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public void ValidOrder(Order aOrder)
        {
            //update by sqlcommand(failed by linq)
            m_CosmeticDC.ExecuteCommand("UPDATE [dbo].[Order] SET Valid=@p0 WHERE OrderId = @p1", aOrder.Valid, aOrder.OrderId);
        }

        /// <summary>
        /// 获得时间最近生成的valid为false的一张订单
        /// </summary>
        /// <param name="aTradeNo"></param>
        /// <returns></returns>
        public Order GetOrderByTradeNo(string aTradeNo)
        {
            //return m_CosmeticDC.Orders.Single(theOrder => theOrder.OrderNum == aTradeNo);
            var result = (from ord in m_CosmeticDC.Orders
                          where ord.OrderNum == aTradeNo
                          orderby ord.CreateTime descending
                          select ord).Take(1).Single();
            return result;
        }

        public Order GetValidOrderByTradeNo(string aTradeNo)
        {
            Order result = (from ord in m_CosmeticDC.Orders
                            where ord.Valid == true
                            && ord.OrderNum == aTradeNo
                            orderby ord.CreateTime descending
                            select ord).Take(1).Single();
            return result;
        }


        public void ClearGarbage(string tradecode)
        {
            Order od = (from order in m_CosmeticDC.Orders where order.OrderNum == tradecode select order).Single();
            if (od != null)
            {
                m_CosmeticDC.OrderDetails.DeleteOnSubmit((from odt in m_CosmeticDC.OrderDetails where odt.OrderId == od.OrderId select odt).Single());
                m_CosmeticDC.Orders.DeleteOnSubmit(od);
                m_CosmeticDC.SubmitChanges();
            }
        }

        /// <summary>
        /// 检查购物车里的商品的在库数量,如果有小于等于0的,返回TURE,否则返回FALSE
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public string CheckGoodsAvailable(Hashtable ht)
        {
            bool flag = false;

            string product_name = "";

            Guid[] pp = new Guid[ht.Count];
            int i = 0;

            foreach (DictionaryEntry entry in ht)
            {
                pp[i] = new Guid(entry.Key.ToString());
                i++;
            }
            var result = (from gpt in m_CosmeticDC.ProductInfos
                          where pp.Contains(gpt.ProductId)
                          select new
                          {
                              ProductId = gpt.ProductId,
                              ProductName = gpt.Name,
                              ProductUnitPrice = gpt.网络零售价.ToString().Remove(gpt.网络零售价.ToString().Length - 2),//double.Parse(gpt.网络零售价.ToString()),//
                              PictureUrl = gpt.PictureUrl,
                              ProductCount = gpt.在库数量
                              //Count = ht[gpt.ProductId.ToString()].ToString(),
                              //ProductSmallPrice ="1"// Convert.ToInt32(ht[gpt.ProductId]) * gpt.Price,
                          }).ToList();


            foreach (var pi in result)
            {
                if (pi.ProductCount < Convert.ToSingle(ht[pi.ProductId.ToString()]))
                {
                    product_name = pi.ProductName;
                    break;
                }
            }

            return product_name;

        }

        /// <summary>
        ///       where theOrder.CreateTime.Day == DateTime.Today.AddDays(-1).Day
        /// </summary>
        /// <returns></returns>
        //public IEnumerable GetPurchaseAmoutTop50()
        //{
        //    var result = from theOrder in m_CosmeticDC.Orders
        //                 join theUser in m_CosmeticDC.Users
        //                 on theOrder.UserId equals theUser.UserId
        //                 orderby theOrder.Price descending
        //                 select new
        //                 { 
        //                     UserName = theUser.UserName,
        //                     PurchaseAmount = theOrder.Price,
        //                     DeliveryAddress = theOrder.DeliveryAddress 
        //                 };
        //    return result.Take(5);
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        //public IEnumerable GetPurchaseTimesTop50()
        //{
        //    var result = from theOrder in m_CosmeticDC.OrderInfos
        //                 where theOrder.出货发送 == "已发送" || theOrder.State == "完成"
        //                 //join theUser in m_CosmeticDC.Users
        //                 //on theOrder.UserId equals theUser.UserId
        //                 group theOrder by theOrder.UserId into g
        //                 select new
        //                 {
        //                     Name = g.Key,
        //                     Count = g.Count()
        //                 };
        //    return (from theResult in result
        //            where 1==1
        //            orderby theResult.Count descending
        //            select new
        //            {
        //                Name = (from theUser in m_CosmeticDC.Users where theUser.UserId == theResult.Name select theUser.UserName).Single(),//(),
        //                Count = theResult.Count
        //            }
        //            ).Take(5);
        //}

        /// <summary>
        /// 根据User取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public List<Order> getOrdersByUser_L(User user)
        {
            var result = from ordInfo in m_CosmeticDC.Orders
                         where ordInfo.UserId == user.UserId
                         select ordInfo;
            return result.ToList();
        }

        /// <summary>
        /// 根据User取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public List<Order> GetOrdersByUser_rh(User user)
        {
            var result = from ordInfo in m_CosmeticDC.Orders
                         where ordInfo.UserId == user.UserId
                         select ordInfo;
            return result.ToList();
        }

        public List<OrderInfo> getOrdersByUser_Ls(xuser user)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.id
                         select ordInfo;
            return result.ToList();
        }


        //public IEnumerable GetAllorder(User user)
        //{
        //    var result = from theFocus in m_CosmeticDC.Orders
        //                 join theProduct in m_CosmeticDC.OrderDetails
        //                 on theFocus.OrderId equals theProduct.OrderId
        //                 where theFocus.UserId == user
        //                 select new
        //                 {
        //                     orderstate =theFocus.State ,
        //                     OrderNum = theFocus.OrderNum,
        //                     CreateTime = theFocus.CreateTime,
        //                     Price = theFocus.Price,
        //                     DeliveryPrice=theFocus .DeliveryPrice,
        //                 };
        //    return result;
        //}

        /// <summary>
        /// 根据订单号取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public List<OrderInfo> getOrderByOrderNum_L(string orderNum)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         select ordInfo;
            result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result.ToList();
        }


        /// <summary>
        /// 根据订单号取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public List<OrderInfo> getOrderByOrderNum_LS(User user, string orderNum)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.UserId
                         select ordInfo;
            result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result.ToList();
        }

        /// <summary>
        /// 根据订单号取出该用户的订单
        /// </summary>
        /// <param name="user">Specific User</param>
        /// <returns></returns>
        public List<OrderInfo> getOrderByOrderNum_LS1(xuser user, string orderNum)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.id
                         select ordInfo;
            result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result.ToList();
        }

        /// <summary>
        /// 根据时间段，查询出用户在此时间段的订单。
        /// </summary>
        /// <param name="user"></param>
        /// <param name="orderNum"></param>
        /// <param name="aDateTime"></param>
        /// <returns></returns>
        public List<OrderInfo> getOrderByData_L(User user, DateTime aDateTime)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.UserId
                         && ordInfo.CreateTime > aDateTime
                         select ordInfo;
            //result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result.ToList();
        }

        /// <summary>
        /// 根据时间段，查询出用户在此时间段的订单。
        /// </summary>
        /// <param name="user"></param>
        /// <param name="orderNum"></param>
        /// <param name="aDateTime"></param>
        /// <returns></returns>
        public List<OrderInfo> getOrderByData_Ls(xuser user, DateTime aDateTime)
        {
            var result = from ordInfo in m_CosmeticDC.OrderInfos
                         where ordInfo.UserId == user.id
                         && ordInfo.CreateTime > aDateTime
                         select ordInfo;
            //result = result.Where(c => SqlMethods.Like(c.OrderNum, "%" + orderNum + "%"));
            return result.ToList();
        }



        /// <summary>
        /// 显示订单的详细内容。
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        /// 

        //public DataTable SearchProductByOrderNum(string orderNum)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    //try
        //    //{
        //        con.Open();

        //        string cmdAdjustPackage = "select PackageId from dbo.OrderDetail where OrderId in( select OrderId from dbo.OrderInfo where OrderNum= '" + orderNum + "')";
        //        SqlCommand cmdadjust = new SqlCommand(cmdAdjustPackage, con);
        //        object result = cmdadjust.ExecuteScalar();
        //        if (result !=null && string.IsNullOrEmpty(result.ToString()))
        //        {

        //            string cmdStr = "select pin.[Name],pin.[PictureUrl],pin.[网络零售价], od.[ProductId],od.[Count],od.[Size] from dbo.OrderDetail as od  inner join ProductInfo as pin on od.ProductCode = pin.ProductCode where od.OrderId in ( select oi.OrderId from OrderInfo as oi where oi.OrderNum='" + orderNum + "' )";
        //            SqlCommand com = new SqlCommand(cmdStr, con);
        //            SqlDataAdapter dataadapt = new SqlDataAdapter(com);
        //            DataSet ds = new DataSet();
        //            dataadapt.Fill(ds);
        //            return ds.Tables[0];
        //        }
        //        //else
        //        //{
        //        //    string packageStr = "select ProductId1,ProductId2,ProductId3 from PackageSales where Id in(select PackageId from dbo.OrderDetail where OrderId in( select OrderId from dbo.OrderInfo where OrderNum= '" + orderNum + "'))";
        //        //    SqlCommand cmdProducts = new SqlCommand(packageStr, con);
        //        //    DataSet ds = new DataSet();
        //        //    SqlDataAdapter adap = new SqlDataAdapter(cmdProducts);
        //        //    adap.Fill(ds);

        //            //if (ds.Tables[0].Rows.Count < 1) return null;

        //            //DataTable dt = new DataTable();
        //            //dt.Columns.Add(new DataColumn("ProductId"));
        //            //dt.Columns.Add(new DataColumn("PictureUrl"));
        //            //dt.Columns.Add(new DataColumn("Name"));
        //            //dt.Columns.Add(new DataColumn("Count"));
        //            //dt.Columns.Add(new DataColumn("网络零售价"));
        //            //DataSet dsPro = GetProductInformation(ds.Tables[0].Rows[0]["ProductId1"].ToString());
        //            //DataRow dr = dt.NewRow();
        //            //dr["ProductId"] = dsPro.Tables[0].Rows[0]["ProductId"].ToString();
        //            //dr["PictureUrl"] = dsPro.Tables[0].Rows[0]["PictureUrl"].ToString();
        //            //dr["Name"] = dsPro.Tables[0].Rows[0]["Name"].ToString();
        //            //dr["Count"] = dsPro.Tables[0].Rows[0]["Count"].ToString();
        //            //dr["网络零售价"] = dsPro.Tables[0].Rows[0]["网络零售价"].ToString();
        //            //dt.Rows.Add(dr);

        //            //DataSet dsPro2 = GetProductInformation(ds.Tables[0].Rows[0]["ProductId2"].ToString());
        //            //DataRow dr2 = dt.NewRow();
        //            //dr2["ProductId"] = dsPro2.Tables[0].Rows[0]["ProductId"].ToString();
        //            //dr2["PictureUrl"] = dsPro2.Tables[0].Rows[0]["PictureUrl"].ToString();
        //            //dr2["Name"] = dsPro2.Tables[0].Rows[0]["Name"].ToString();
        //            //dr2["Count"] = dsPro2.Tables[0].Rows[0]["Count"].ToString();
        //            //dr2["网络零售价"] = dsPro2.Tables[0].Rows[0]["网络零售价"].ToString();
        //            //dt.Rows.Add(dr2);

        //            //DataSet dsPro3 = GetProductInformation(ds.Tables[0].Rows[0]["ProductId3"].ToString());
        //            //DataRow dr3 = dt.NewRow();
        //            //dr3["ProductId"] = dsPro3.Tables[0].Rows[0]["ProductId"].ToString();
        //            //dr3["PictureUrl"] = dsPro3.Tables[0].Rows[0]["PictureUrl"].ToString();
        //            //dr3["Name"] = dsPro3.Tables[0].Rows[0]["Name"].ToString();
        //            //dr3["Count"] = dsPro3.Tables[0].Rows[0]["Count"].ToString();
        //            //dr3["网络零售价"] = dsPro3.Tables[0].Rows[0]["网络零售价"].ToString();
        //            //dt.Rows.Add(dr3);

        //            return dt;
        //        }


        //    finally
        //    {
        //        con.Close();

        //    }
        //}

        public IEnumerable SearchProductByOrderNum(string orderNum)
        {
            var result = from theFocus in m_CosmeticDC.OrderDetails
                         join theProduct in m_CosmeticDC.Products
                         on theFocus.ProductId equals theProduct.ProductId
                         join thef in m_CosmeticDC.Orders on
                         theFocus.OrderId equals thef.OrderId
                         where thef.OrderNum == orderNum

                         select new
                         {
                             ProductId = theFocus.ProductId,
                             PictureUrl = theProduct.PictureUrl,
                             Name = theProduct.Name,
                             Count = theFocus.Count,
                             price = theProduct.eprice,

                         };
            return result;
        }

        /// <summary>
        /// 显示订单的详细内容。
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public DataSet GetUserOrderValues(string selectItem, string content)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = string.Empty;
                switch (selectItem)
                {
                    case "会员email": { cmdStr = "select us.[UserName],us.[Email], us.[NickName],oi.[OrderNum],oi.[State],oi.[出货发送] from [OrderInfo] as oi inner join [User] as us on oi.[UserId]= us.[UserId] where us.[Email]='" + content + "'"; } break;
                    case "会员昵称": { cmdStr = "select us.[UserName],us.[Email], us.[NickName],oi.[OrderNum],oi.[State],oi.[出货发送] from [OrderInfo] as oi inner join [User] as us on oi.[UserId]= us.[UserId] where us.[NickName]='" + content + "'"; } break;
                    case "会员姓名": { cmdStr = " select us.[UserName],us.[Email], us.[NickName],oi.[OrderNum],oi.[State],oi.[出货发送] from [OrderInfo] as oi inner join [User] as us on oi.[UserId]= us.[UserId] where us.[UserName]='" + content + "'"; } break;
                    case "订单号": { cmdStr = "select us.[UserName],us.[Email], us.[NickName],oi.[OrderNum],oi.[State],oi.[出货发送] from [OrderInfo] as oi inner join [User] as us on oi.[UserId]= us.[UserId] where oi.[OrderNum]='" + content + "'"; } break;
                    default:
                        break;
                }
                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter dataadapt = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                dataadapt.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();

            }
        }

        /// <summary>
        /// 更新订单收发货状态。
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public DataSet GetProductInformation(string productId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select  ProductId,PictureUrl,[Name],1 [Count], [网络零售价] from ProductInfo where ProductId = '" + productId + "'";

                SqlCommand com = new SqlCommand(cmdStr, con);
                SqlDataAdapter dataadapt = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                dataadapt.Fill(ds);
                return ds;

            }
            finally
            {
                con.Close();

            }
        }


        /// <summary>
        /// 更新订单收发货状态。
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public void UpdataState(string orderNum, string state)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "update [Order] set State ='" + state + "' where OrderNum='" + orderNum + "'";

                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

                //added by richard
                string cmdStr2 = "update [Order] set CompleteTime = '" + DateTime.Now.ToString() + "' where OrderNum='" + orderNum + "'";

                SqlCommand com2 = new SqlCommand(cmdStr2, con);
                com2.ExecuteNonQuery();
            }
            finally
            {
                con.Close();

            }
        }

        public List<Address> GetAddressByUserId(Guid UserId)
        {
            var result = (from TheAddress in m_CosmeticDC.Address
                          where TheAddress.UserId == UserId
                          select TheAddress).ToList();
            return result;

        }


        /// <summary>
        /// 增加会员的积分。
        /// </summary>
        /// <param name="orderNum"></param>
        public void AddIntegral(string orderNum)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                //查寻会员的当前的级别
                string cmdStr = "select us.UserId, us.[Birthday], us.Integral,us.[Level],oi.[总金额] from [User] as us inner join OrderInfo as oi on us.UserId = oi.UserId where oi.OrderNum='" + orderNum + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                DateTime Birthday = Convert.ToDateTime(ds.Tables[0].Rows[0]["Birthday"].ToString());
                string userId = ds.Tables[0].Rows[0]["UserId"].ToString();
                int level = Convert.ToInt32(string.IsNullOrEmpty(ds.Tables[0].Rows[0]["Level"].ToString()) ? "1" : ds.Tables[0].Rows[0]["Level"].ToString());
                decimal allMoney = Convert.ToDecimal(ds.Tables[0].Rows[0]["总金额"].ToString());
                //查寻会员当前级别的积分赠比率。
                string cmdStr2 = "select DiscountRate from dbo.MemberRegulation where other = " + level + "";
                SqlCommand comIntegral = new SqlCommand(cmdStr2, con);
                object rate = comIntegral.ExecuteScalar();
                //修改会员积分。

                if (Birthday.Month == DateTime.Now.Month && Birthday.Day == DateTime.Now.Day) //生日会员购买商品
                {
                    switch (level)
                    {
                        //白金会员生日会员购买商品，积分翻倍
                        case 3:
                            {
                                decimal addIntegral = allMoney * Convert.ToDecimal(rate) * 2;
                                string cmdStr3 = "update [User] set Integral = Integral+ " + addIntegral + "  where UserId='" + userId + "'";
                                SqlCommand comAddIntegral = new SqlCommand(cmdStr3, con);
                                comAddIntegral.ExecuteNonQuery();
                            }; break;
                        //钻石会员生日会员购买商品，积分翻倍
                        case 4:
                            {
                                decimal addIntegral = allMoney * Convert.ToDecimal(rate) * 2;
                                string cmdStr3 = "update [User] set Integral = Integral+ " + addIntegral + "  where UserId='" + userId + "'";
                                SqlCommand comAddIntegral = new SqlCommand(cmdStr3, con);
                                comAddIntegral.ExecuteNonQuery();
                            }; break;
                        //至尊粉钻会员生日会员购买商品，积分3倍
                        case 5:
                            {
                                decimal addIntegral = allMoney * Convert.ToDecimal(rate) * 3;
                                string cmdStr3 = "update [User] set Integral = Integral+ " + addIntegral + "  where UserId='" + userId + "'";
                                SqlCommand comAddIntegral = new SqlCommand(cmdStr3, con);
                                comAddIntegral.ExecuteNonQuery();
                            }; break;
                        default:
                            {
                                decimal addIntegral = allMoney * Convert.ToDecimal(rate);
                                string cmdStr3 = "update [User] set Integral = Integral+ " + addIntegral + "  where UserId='" + userId + "'";
                                SqlCommand comAddIntegral = new SqlCommand(cmdStr3, con);
                                comAddIntegral.ExecuteNonQuery();
                            }
                            break;
                    }
                }
                else
                {

                    decimal addIntegral = allMoney * Convert.ToDecimal(rate);
                    string cmdStr3 = "update [User] set Integral = Integral+ " + addIntegral + "  where UserId='" + userId + "'";
                    SqlCommand comAddIntegral = new SqlCommand(cmdStr3, con);
                    comAddIntegral.ExecuteNonQuery();
                }
            }
            finally
            {
                con.Close();

            }

        }

        /// <summary>
        /// 查询订单的动费。
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public decimal SearchOrderDeliveryPrice(string orderNum)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select DeliveryPrice from dbo.[Order] where OrderNum ='" + orderNum + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                object pri = com.ExecuteScalar();
                if (!string.IsNullOrEmpty(pri.ToString()))
                {
                    return Convert.ToDecimal(pri);
                }
                else
                {
                    return 0;
                }
            }
            finally
            {
                con.Close();

            }
        }

    }
}
